﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DBBase;
using System.IO;

using System.Reflection; // 引用这个才能使用Missing字段
using System.Xml;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Business
{
    public partial class TestItems : Form
    {
       

        public string[] ccllx;
        public string[] ccllxEX;
        public int ijilu;
        public TestItems()
        {
            InitializeComponent();
           
            ijilu = 0;
            ccllx = new string[40] { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" };
            ccllxEX = new string[40] { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" };
            getcllx();
        }
     
        public void getcllx()
        {
            Query mquery = new Query();
            string sql;
            sql = string.Format(@"Select distinct AT_CLLX from Autos ");

            ccllx[0] = "DET_SPEEDJL";
            ccllx[1] = "DET_SLIPJL";
            ccllx[2] = "DET_ZWDJL";
            ccllx[3] = "DET_YWDJL";
            ccllx[4] = "DET_ZZJL1";
            ccllx[5] = "DET_ZZJL2";
            ccllx[6] = "DET_ZZJL3";
            ccllx[7] = "DET_ZZJL4";
            ccllx[8] = "DET_ZZJL5";
            ccllx[9] = "DET_SSJL";

            ccllxEX[0] = "PD_SPEED";
            ccllxEX[1] = "PD_SLIP";
            ccllxEX[2] = "PD_ZWDGQ";
            ccllxEX[3] = "PD_YWDGQ";
            ccllxEX[4] = "PD_BRIKE1";
            ccllxEX[5] = "PD_BRIKE2";
            ccllxEX[6] = "PD_BRIKE3";
            ccllxEX[7] = "PD_BRIKE4";
            ccllxEX[8] = "PD_BRIKE5";
            ccllxEX[9] = "PD_ZHUCHE";
            ijilu = 10;
        }
        public void getneirong()
        {
            string sql = "";
            string sql2 = "";
            string sql3 = "";
            string sql4 = "";
            Query mquery = new Query();
            for (int i = 0; i < ijilu; i++)
            {
                sql = string.Format(@"Select AT_CLLX from Autos,Examines,Detects Where Examines.EXAMINE_ID=Detects.EXAMINE_ID and Examines.AT_ID=Autos.AT_ID and AT_CLLX='{0}'", ccllx[i]);

                int izong = mquery.selectDBcount(sql);

                sql2 = string.Format(@"Select AT_CLLX from Autos,Examines,Detects Where Examines.EXAMINE_ID=Detects.EXAMINE_ID and Examines.AT_ID=Autos.AT_ID and AT_CLLX='{0}' and DET_OK=1", ccllx[i]);

                int izong2 = mquery.selectDBcount(sql2);

                sql3 = string.Format(@"Select AT_CLLX from Autos,Examines,Detects Where Examines.EXAMINE_ID=Detects.EXAMINE_ID and Examines.AT_ID=Autos.AT_ID and AT_CLLX='{0}' and DET_OK=1 and DET_ITERAT<1", ccllx[i]);

                int izong3 = mquery.selectDBcount(sql3);

                sql4 = string.Format(@"Select AT_CLLX from Autos,Examines,Detects Where Examines.EXAMINE_ID=Detects.EXAMINE_ID and Examines.AT_ID=Autos.AT_ID and AT_CLLX='{0}' and DET_OK=1 and DET_ITERAT>0", ccllx[i]);

                int izong4 = mquery.selectDBcount(sql4);
            }
        }

        private void skinButton1_Click(object sender, EventArgs e)
        {
            DataTable dtable = new DataTable("Rock");
            //set columns names
            dtable.Columns.Add("1", typeof(System.String));
            dtable.Columns.Add("2", typeof(System.String));
            dtable.Columns.Add("3", typeof(System.String));
            dtable.Columns.Add("4", typeof(System.String));
            dtable.Columns.Add("5", typeof(System.String));
            dtable.Columns.Add("6", typeof(System.String));
            dtable.Columns.Add("7", typeof(System.String));
            dtable.Columns.Add("8", typeof(System.String));
            dtable.Columns.Add("9", typeof(System.String));
            dtable.Columns.Add("10", typeof(System.String));
            dtable.Columns.Add("11", typeof(System.String));

            Query mquery = new Query();
            string sql = "";
            string sql2 = "";
            string sql3 = "";
            string sql4 = "";
            int izs1 = 0;
            int izs2 = 0;
            int izs3 = 0;
            int izs4 = 0;
            DataRow drow = dtable.NewRow();
            for (int i = 0; i < ijilu; i++)
            {
                sql = string.Format(@"Select AT_CLLX from  [dbo].[AUTO],Detects_SJA,Detects_SJB Where AUTO.AT_ID=Detects_SJA.AT_ID and Detects_SJA.DETECT_ID=Detects_SJB.DETECT_ID  and {0} is not null and Detects_SJA.DET_DATE>'{1}' and Detects_SJA.DET_DATE<'{2}'", ccllx[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong = mquery.selectDBcount(sql);
                izs1 = izs1 + izong;

                sql2 = string.Format(@"Select Detects_SJA.DETECT_ID from  [dbo].[AUTO],Detects_SJA,PD_AJ,Detects_SJB Where AUTO.AT_ID=Detects_SJA.AT_ID and PD_AJ.DETECT_ID=Detects_SJA.DETECT_ID and Detects_SJA.DETECT_ID=Detects_SJB.DETECT_ID 
                and {0} is not null and {1} like '%%不合格%%' and Detects_SJA.DET_DATE>'{2}' and Detects_SJA.DET_DATE<'{3}'", ccllx[i], ccllxEX[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong2 = mquery.selectDBcount(sql2);
                izs2 = izs2 + izong2;

                sql3 = string.Format(@"Select Detects_SJA.DETECT_ID from  [dbo].[AUTO],Detects_SJA,PD_AJ,Detects_SJB Where AUTO.AT_ID=Detects_SJA.AT_ID and PD_AJ.DETECT_ID=Detects_SJA.DETECT_ID and Detects_SJA.DETECT_ID=Detects_SJB.DETECT_ID 
                and {0} is not null and {1} like '%%不合格%%' and Detects_SJA.DET_DATE>'{2}' and Detects_SJA.DET_DATE<'{3}' and DET_JYCS=1", ccllx[i],ccllxEX[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong3 = mquery.selectDBcount(sql3);
                izs3 = izs3 + izong3;

                sql4 = string.Format(@"Select Detects_SJA.DETECT_ID from  [dbo].[AUTO],Detects_SJA,Detects_SJB,PD_AJ Where AUTO.AT_ID=Detects_SJA.AT_ID and Detects_SJA.DETECT_ID=Detects_SJB.DETECT_ID and PD_AJ.DETECT_ID=Detects_SJA.DETECT_ID and
 {0} is not null and  {1} like '%%不合格%%' and Detects_SJA.DET_DATE>'{2}' and Detects_SJA.DET_DATE<'{3}' and DET_JYCS>1", ccllx[i],ccllxEX[i], date_kaishi.Value.ToShortDateString() + " 00:00:00", date_jieshu.Value.ToShortDateString() + " 23:59:59");

                int izong4 = mquery.selectDBcount(sql4);
                izs4 = izs4 + izong4;

                drow = dtable.NewRow();
                drow["1"] = string.Format("{0}", i + 1);
                string sjyxm = "";
                if (ccllx[i] == "DET_SPEEDJL")
                {
                    sjyxm = "车速表检验";
                }
                else if (ccllx[i] == "DET_SLIPJL")
                {
                    sjyxm = "侧滑检验";
                }
                else if (ccllx[i] == "DET_ZWDJL")
                {
                    sjyxm = "左主灯检验";
                }
                else if (ccllx[i] == "DET_YWDJL")
                {
                    sjyxm = "右主灯检验";
                }
                else if (ccllx[i] == "DET_ZZJL1")
                {
                    sjyxm = "一轴制动检验";
                }
                else if (ccllx[i] == "DET_ZZJL2")
                {
                    sjyxm = "二轴制动检验";
                }
                else if (ccllx[i] == "DET_ZZJL3")
                {
                    sjyxm = "三轴制动检验";
                }
                else if (ccllx[i] == "DET_ZZJL4")
                {
                    sjyxm = "四轴制动检验";
                }
                else if (ccllx[i] == "DET_ZZJL5")
                {
                    sjyxm = "五轴制动检验";
                }
                else if (ccllx[i] == "DET_SSJL")
                {
                    sjyxm = "驻车检验";
                }
                drow["2"] = sjyxm;
                drow["3"] = string.Format("{0}", izong);
                drow["4"] = string.Format("{0}", izong - izong2);//izong2);
                drow["5"] = string.Format("{0}%", 100 * (izong - izong2) / (izong > 0 ? izong : 1));//100 * izong2 / (izong > 0 ? izong : 1));
                drow["6"] = string.Format("{0}", izong2);//izong - izong2);
                drow["7"] = string.Format("{0}%", 100 * izong2 / (izong > 0 ? izong : 1));// 100 * (izong - izong2) / (izong > 0 ? izong : 1));
                drow["8"] = string.Format("{0}", izong-izong3);
                drow["9"] = string.Format("{0}%", 100 * (izong - izong3) / (izong > 0 ? izong : 1));
                drow["10"] = string.Format("{0}", izong4);
                drow["11"] = string.Format("{0}%", 100 * (izong - izong4 == 0 ? izong : izong4) / (izong > 0 ? izong : 1));
                dtable.Rows.Add(drow);
            }

            drow = dtable.NewRow();
            drow["1"] = string.Format("{0}", ijilu + 1);

            drow["2"] = "合计";
            drow["3"] = string.Format("{0}", izs1);
            drow["4"] = string.Format("{0}", izs1 - izs2);//izong2);
            drow["5"] = string.Format("{0}%", 100 * (izs1 - izs2) / (izs1 > 0 ? izs1 : 1));//100 * izong2 / (izong > 0 ? izong : 1));
            drow["6"] = string.Format("{0}", izs2);//izong - izong2);
            drow["7"] = string.Format("{0}%", 100 * izs2 / (izs1 > 0 ? izs1 : 1));// 100 * (izong - izong2) / (izong > 0 ? izong : 1));
            drow["8"] = string.Format("{0}", izs1 - izs3);
            drow["9"] = string.Format("{0}%", 100 * (izs1-izs3) / (izs1 > 0 ? izs1 : 1));
            drow["10"] = string.Format("{0}", izs4);
            drow["11"] = string.Format("{0}%", 100 * (izs1 - izs4 == 0 ? izs1 : izs4) / (izs1 > 0 ? izs1 : 1));
            dtable.Rows.Add(drow);
            multiColHeaderDgv2.DataSource = dtable;
        }
      
     
    }
}
